//
//  ViewController.m
//  SQL演练
//
//  Created by Guo Rui on 2017/10/25.
//  Copyright © 2017年 GR Harry. All rights reserved.
//

#import "ViewController.h"
#import <sqlite3.h>
#import "Student.h"
#import "SQLChain.h"

#import <FMDB.h>
@interface ViewController ()

@end

@implementation ViewController 
{
//    FMDatabase *_db;
    
    // 线程安全
    FMDatabaseQueue *_queue;
}

- (void)viewDidLoad {
    [super viewDidLoad];

//    SQLChain *sql = [[SQLChain alloc] init];
//    [sql databaseWithName:@"sss"];
    
//    [sql safeThreadHandle:^(SQLChain *chain) {
//        chain.createTable(@"t_class").fieldsDefine(@"(id INTEGER PRIMARY KEY AUTOINCREMENT, name text NOT NULL)").end();
//        chain.createTable(@"t_student").fieldsDefine(@"(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, class_id INTEGER, CONSTRAINT fk_student_id_class_id FOREIGN KEY (class_id) REFERENCES t_class(id))").end();
    
//        chain.insert(@"t_class").fields(@[@"name"]).values(@[@"15级1班"]).end();
//        chain.insert(@"t_class").fields(@[@"name"]).values(@[@"16级2班"]).end();
//        chain.insert(@"t_class").fields(@[@"name"]).values(@[@"17级3班"]).end();
        
        
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"jack", @1]).end();
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"rose", @2]).end();
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"jim", @3]).end();
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"tik", @1]).end();
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"ban", @3]).end();
//        chain.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"lili", @2]).end();
//    }];
    
    
    
//    [self openDB];
//    [self queryJoinTable];
//    [self createTable];
//    [self insertRecord];
//    [self queryCount];
    
//    NSLog(@"%@", [self queryAll]);
}

- (void)queryCount
{
    NSString *sql = @"SELECT count(*) FROM t_student";// avg(age)
    
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
        FMResultSet *set = [db executeQuery:sql];
        
        while ([set next]) {
            int count = [set intForColumn:@"count(*)"];// avg(age)
            NSLog(@"%zd", count);
        }
    }];
}

- (NSArray *)queryAll
{
    NSString *sql = @"SELECT * FROM t_student";
    
    NSMutableArray *results = [NSMutableArray arrayWithCapacity:0];
    
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
        
        FMResultSet *set = [db executeQuery:sql];
        
        while ([set next]) {
            // set的每个node相当于 一条记录
            NSString *name = [set stringForColumn:@"name"];
            NSInteger age = [set intForColumn:@"age"];
            CGFloat score = [set doubleForColumn:@"score"];
            
            NSDictionary *dic = @{ @"name": name, @"age": @(age), @"score": @(score) };
            [results addObject:dic];
        }
    }];
    
    return results;
}

- (void)batchInsertRecord
{
    NSMutableString *sqls = [NSMutableString stringWithCapacity:0];
    for (int i = 0; i < 100000; i++) {
        [sqls stringByAppendingString:@"INSERT INTO t_student (name,age,score) VALUES ('张三',15,99);"];
    }
    //    NSString *sql = @"INSERT INTO t_student (name,age,score) VALUES ('张三',15,99)";
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
        //        BOOL success = [db executeUpdate:sql];
        [db executeStatements:sqls withResultBlock:^int(NSDictionary * _Nonnull resultsDictionary) {
            NSLog(@"%@", resultsDictionary);
            return 1;
        }];
        //        if (success) {
        //            NSLog(@"插入成功");
        //        }
        //        else {
        //            NSLog(@"插入失败");
        //        }
    }];
}

- (void)insertRecord
{
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
//        BOOL success = [db executeUpdate:@"INSERT INTO t_student (name,age,score) VALUES (?,?,?)", @"哈哈", @(23), @(89)];
        BOOL success = [db executeUpdate:@"INSERT INTO t_student VALUES (:name,:age,:score)" withParameterDictionary:@{ @"name": @"宝宝", @"age": @(23), @"score": @(98) }];
//        BOOL success = [db executeUpdate:@"INSERT INTO t_student VALUES (?,?,?,?)" withArgumentsInArray:@[ @23, @"sss", @(23), @(98) ]];
//        BOOL success = [db executeUpdateWithFormat:@"INSERT INTO t_student (name,age,score) VALUES (%@,%d,%f)", @"qq", 23, 89.0];
        if (success) {
            NSLog(@"插入成功");
        }
        else {
            NSLog(@"插入失败");
        }
    }];
}

- (void)openDB
{
    _queue = [FMDatabaseQueue databaseQueueWithPath:@"/Users/guorui/Desktop/dataBase/haha.sqlite"];
    
}

- (void)createTable
{
    NSString *sql =
    @"CREATE TABLE IF NOT EXISTS t_class(id INTEGER PRIMARY KEY AUTOINCREMENT, name text NOT NULL);";
    sql = [sql stringByAppendingString:@"CREATE TABLE IF NOT EXISTS t_student(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, class_id INTEGER, CONSTRAINT fk_student_id_class_id FOREIGN KEY (class_id) REFERENCES t_class(id));"];
    
    sql = [sql stringByAppendingString:@"INSERT INTO t_class (name) VALUES ('15级1班');INSERT INTO t_class (name) VALUES ('16级2班');INSERT INTO t_class (name) VALUES ('17级3班');"];
    
    sql = [sql stringByAppendingString:@"INSERT INTO t_student (name, class_id) VALUES ('jack', 1);INSERT INTO t_student (name, class_id) VALUES ('rose', 2);INSERT INTO t_student (name, class_id) VALUES ('jim', 3);INSERT INTO t_student (name, class_id) VALUES ('tik', 1);INSERT INTO t_student (name, class_id) VALUES ('ban', 3);INSERT INTO t_student (name, class_id) VALUES ('lusi', 2);INSERT INTO t_student (name, class_id) VALUES ('lili', 2);"];
    
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db executeStatements:sql]) {
            NSLog(@"建表成功");
        }
        else {
            NSLog(@"建表失败");
        }
    }];
    
}

- (void)queryJoinTable
{
    // 查出 15级1班 的所有人

    NSString *sql = @"SELECT tc.id as class_id, tc.name as class_name, ts.id as student_id, ts.name as student_name FROM t_class as tc, t_student as ts WHERE tc.id = ts.class_id AND tc.name = '15级1班'";
    // SELECT tc.id as class_id, tc.name as class_name,ts.id as student_id, ts.name as student_name FROM t_class as tc, t_student as ts WHERE ts.class_id = tc.id;
    [_queue inDatabase:^(FMDatabase * _Nonnull db) {
        FMResultSet *set = [db executeQuery:sql];
        
        while ([set next]) {
            int class_id = [set intForColumn:@"class_id"];
            int student_id = [set intForColumn:@"student_id"];
            NSString *class_name = [set stringForColumn:@"class_name"];
            NSString *student_name = [set stringForColumn:@"student_name"];
//            NSLog(@"%zd -- %@", ID, name);
            NSLog(@"%zd -- %@, %zd -- %@", class_id, class_name, student_id, student_name);
            
        }
    }];
}



@end
